--    Program: 		CAPPOEXT_ATTACHMENT.sql
--    Description:	Extract FND Attachment information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   number;
  l_processed_count_1 	   number;
  l_rejected_count_1  	   number;
  l_processed_count_2	   number;
  l_rejected_count_2	   number;
  l_processed_count_3	   number;
  l_rejected_count_3	   number;
  l_err_msg       		   varchar2(2000);
  
  l_utl_file_1  	       utl_file.file_type;
  l_utl_file_2 		       utl_file.file_type;
  l_utl_file_3 		       utl_file.file_type;
  l_filename_1 		       varchar2(255);
  l_filename_2 		       varchar2(255);
  l_filename_3 		       varchar2(255);
  l_pathname  		       varchar2(100);
  
  l_set_of_books_name	   varchar2(150);
  l_set_of_books_id		   number;
  
  cursor cur_attachment  (p_set_of_books_id number) is 
	select fad.* 
    from   fnd_attached_documents fad,
    	   po_headers_all ph,
    	   hr_organization_information hoi
    where  fad.entity_name = 'PO_HEADERS'
    and    fad.pk1_value = ph.po_header_id
    and    ph.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 = to_char(p_set_of_books_id)
    union all
    select fad.* 
    from   fnd_attached_documents fad,
    	   po_lines_all pl,
    	   hr_organization_information hoi
    where  fad.entity_name in ('PO_LINES', 'PO_IN_GA_LINES')
    and    fad.pk1_value = pl.po_line_id
    and    pl.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 = to_char(p_set_of_books_id)
    union all
    select fad.* 
    from   fnd_attached_documents fad,
    	   po_requisition_headers_all prh,
    	   hr_organization_information hoi
    where  fad.entity_name = 'REQ_HEADERS'
    and    fad.pk1_value = prh.requisition_header_id
    and    prh.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 = to_char(p_set_of_books_id)
    union all
    select fad.* 
    from   fnd_attached_documents fad,
    	   po_requisition_lines_all prl,
    	   hr_organization_information hoi
    where  fad.entity_name = 'REQ_LINES'
    and    fad.pk1_value = prl.requisition_line_id
    and    prl.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 = to_char(p_set_of_books_id);
	
  cursor cur_document_b (P_DOCUMENT_ID number) is
	select *
	from   FND_DOCUMENTS
	where  DOCUMENT_ID = P_DOCUMENT_ID;
	
  cursor cur_document_tl (P_DOCUMENT_ID number) is
	select *
	from   FND_DOCUMENTS_TL
	where  DOCUMENT_ID = P_DOCUMENT_ID;


begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPPO_ATTACHMENT_ATTACHED_DOCUMENT.dat';
  l_filename_2 := 'CAPPO_ATTACHMENT_DOCUMENT_B.dat';
  l_filename_3 := 'CAPPO_ATTACHMENT_DOCUMENT_TL.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  l_utl_file_3 := utl_file.fopen(l_pathname, l_filename_3, 'w', 32767);
  
  l_count := 0;
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
  l_processed_count_3 := 0;
  l_rejected_count_3  := 0;
                                         
  l_set_of_books_name := '&1';
  
  begin 
    select set_of_books_id 
	into   l_set_of_books_id 
	from   gl_sets_of_books 
	where  upper(short_name) = upper(l_set_of_books_name);
  exception when others then
   dbms_output.put_line('set_of_books_mame '||l_set_of_books_name|| ' not found');
  end;	
  
  for cur_att in cur_attachment(l_set_of_books_id) loop 
    utl_file.put_line( l_utl_file_1, 
      cur_att.ATTACHED_DOCUMENT_ID      || chr(9) ||
      cur_att.DOCUMENT_ID               || chr(9) ||
      cur_att.CREATION_DATE             || chr(9) ||
      cur_att.CREATED_BY                || chr(9) ||
      cur_att.LAST_UPDATE_DATE          || chr(9) ||
      cur_att.LAST_UPDATED_BY           || chr(9) ||
      cur_att.LAST_UPDATE_LOGIN         || chr(9) ||
      cur_att.SEQ_NUM                   || chr(9) ||
      cur_att.ENTITY_NAME               || chr(9) ||
      cur_att.PK1_VALUE                 || chr(9) ||
      cur_att.PK2_VALUE                 || chr(9) ||
      cur_att.PK3_VALUE                 || chr(9) ||
      cur_att.PK4_VALUE                 || chr(9) ||
      cur_att.PK5_VALUE                 || chr(9) ||
      cur_att.AUTOMATICALLY_ADDED_FLAG  || chr(9) ||
      cur_att.PROGRAM_APPLICATION_ID    || chr(9) ||
      cur_att.PROGRAM_ID                || chr(9) ||
      cur_att.PROGRAM_UPDATE_DATE       || chr(9) ||
      cur_att.REQUEST_ID                || chr(9) ||
      cur_att.ATTRIBUTE_CATEGORY        || chr(9) ||
      cur_att.ATTRIBUTE1                || chr(9) ||
      cur_att.ATTRIBUTE2                || chr(9) ||
      cur_att.ATTRIBUTE3                || chr(9) ||
      cur_att.ATTRIBUTE4                || chr(9) ||
      cur_att.ATTRIBUTE5                || chr(9) ||
      cur_att.ATTRIBUTE6                || chr(9) ||
      cur_att.ATTRIBUTE7                || chr(9) ||
      cur_att.ATTRIBUTE8                || chr(9) ||
      cur_att.ATTRIBUTE9                || chr(9) ||
      cur_att.ATTRIBUTE10               || chr(9) ||
      cur_att.ATTRIBUTE11               || chr(9) ||
      cur_att.ATTRIBUTE12               || chr(9) ||
      cur_att.ATTRIBUTE13               || chr(9) ||
      cur_att.ATTRIBUTE14               || chr(9) ||
      cur_att.ATTRIBUTE15               || chr(9) ||
      cur_att.COLUMN1                   || chr(9) ||
      cur_att.APP_SOURCE_VERSION        || chr(9) ||
      cur_att.CATEGORY_ID               || chr(9) ||
      cur_att.STATUS 
    );
	l_processed_count_1 := l_processed_count_1 + 1;  
	
      for cur_docb in cur_document_b(cur_att.DOCUMENT_ID) loop     
        utl_file.put_line( l_utl_file_2,        
          cur_docb.DOCUMENT_ID             || chr(9) ||
          cur_docb.CREATION_DATE           || chr(9) ||
          cur_docb.CREATED_BY              || chr(9) ||
          cur_docb.LAST_UPDATE_DATE        || chr(9) ||
          cur_docb.LAST_UPDATED_BY         || chr(9) ||
          cur_docb.LAST_UPDATE_LOGIN       || chr(9) ||
          cur_docb.DATATYPE_ID             || chr(9) ||
          cur_docb.CATEGORY_ID             || chr(9) ||
          cur_docb.SECURITY_TYPE           || chr(9) ||
          cur_docb.SECURITY_ID             || chr(9) ||
          cur_docb.PUBLISH_FLAG            || chr(9) ||
          cur_docb.IMAGE_TYPE              || chr(9) ||
          cur_docb.STORAGE_TYPE            || chr(9) ||
          cur_docb.USAGE_TYPE              || chr(9) ||
          cur_docb.START_DATE_ACTIVE       || chr(9) ||
          cur_docb.END_DATE_ACTIVE         || chr(9) ||
          cur_docb.REQUEST_ID              || chr(9) ||
          cur_docb.PROGRAM_APPLICATION_ID  || chr(9) ||
          cur_docb.PROGRAM_ID              || chr(9) ||
          cur_docb.PROGRAM_UPDATE_DATE     || chr(9) ||
          cur_docb.APP_SOURCE_VERSION      || chr(9) ||
          cur_docb.DM_NODE                 || chr(9) ||
          cur_docb.DM_FOLDER_PATH          || chr(9) ||
          cur_docb.DM_TYPE                 || chr(9) ||
          cur_docb.DM_DOCUMENT_ID          || chr(9) ||
          cur_docb.DM_VERSION_NUMBER   
        );
        l_processed_count_2 := l_processed_count_2 + 1;
      end loop;
      	
	  for cur_doctl in cur_document_tl(cur_att.DOCUMENT_ID) loop
        utl_file.put_line( l_utl_file_3,           
          cur_doctl.DOCUMENT_ID             || chr(9) ||
          cur_doctl.CREATION_DATE           || chr(9) ||
          cur_doctl.CREATED_BY              || chr(9) ||
          cur_doctl.LAST_UPDATE_DATE        || chr(9) ||
          cur_doctl.LAST_UPDATED_BY         || chr(9) ||
          cur_doctl.LAST_UPDATE_LOGIN       || chr(9) ||
          cur_doctl.LANGUAGE                || chr(9) ||
          cur_doctl.DESCRIPTION             || chr(9) ||
          cur_doctl.SHORT_TEXT              || chr(9) ||
          cur_doctl.FILE_NAME               || chr(9) ||
          cur_doctl.MEDIA_ID                || chr(9) ||
          cur_doctl.REQUEST_ID              || chr(9) ||
          cur_doctl.PROGRAM_APPLICATION_ID  || chr(9) ||
          cur_doctl.PROGRAM_ID              || chr(9) ||
          cur_doctl.PROGRAM_UPDATE_DATE     || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE_CATEGORY  || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE1          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE2          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE3          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE4          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE5          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE6          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE7          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE8          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE9          || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE10         || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE11         || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE12         || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE13         || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE14         || chr(9) ||
          cur_doctl.DOC_ATTRIBUTE15         || chr(9) ||
          cur_doctl.APP_SOURCE_VERSION      || chr(9) ||
          cur_doctl.SOURCE_LANG          
        );
	  
        l_processed_count_3 := l_processed_count_3 + 1;	  
      end loop;
  end loop;
  
  l_err_msg := '' ;
  if l_processed_count_1 = 0 then
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_set_of_books_name||': Attachment Document B not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPO_ATTACHMENT_ATTACHED_DOCUMENT', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
  
  l_err_msg := '' ;
  if l_processed_count_2 = 0 then
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_set_of_books_name||': Attachment Document TL not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPO_ATTACHMENT_DOCUMENT_B', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
  
  l_err_msg := '' ;
  if l_processed_count_3 = 0 then
     l_rejected_count_3 := l_rejected_count_3 + 1;
     l_err_msg := l_set_of_books_name||': Attacheed Document not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPO_ATTACHMENT_DOCUMENT_TL', sysdate, l_processed_count_3, l_rejected_count_3, l_processed_count_3-l_rejected_count_3, l_err_msg );
  	    
  commit;
  
  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
  utl_file.fclose(l_utl_file_3);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
